﻿Imports System.Data
Imports System.IO
Imports VSModel
Imports Cls_Common

Partial Class Control_CertifiedHousehold_ImportFromExcel
    Inherits System.Web.UI.UserControl
    Private Shared ReadOnly log As log4net.ILog = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType)
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim script As ScriptManager = ScriptManager.GetCurrent(Me.Page)
            If Not script Is Nothing AndAlso script.IsInAsyncPostBack Then
                ScriptManager.RegisterStartupScript(Me.Page, Me.Page.GetType, "regjs", "ajaxJquery()", True)
            Else
                Me.Page.ClientScript.RegisterStartupScript(Me.Page.GetType(), "regjs", String.Concat("Sys.Application.add_load(function(){", "ajaxJquery()", "});"), True)
            End If
        End If
    End Sub

    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        Dim _curHuyenId As Integer = Session("HuyenId")
        If txtFileNameUpload.Value.Trim <> "" Then
            Using data As New VSEntities
                Dim FilePath As String = ""
                Try
                    Dim relativePath As String = Server.MapPath("~") & "\Uploads\"
                    'Complete path of the file.
                    FilePath = relativePath + txtFileNameUpload.Value
                    If Not File.Exists(Server.MapPath("~") & "\Uploads\" & txtFileNameUpload.Value) Then
                        Excute_Javascript("Alertbox('File không tồn tại trên server !');", Me.Page, True)
                        Exit Sub
                    End If
                    '' Get Data from file 
                    Dim dt As DataTable = New DataTable()
                    ' Dữ liệu đọc từ row thứ 6 ~~ [index =5]
                    'dt = ReadExcelToDT(FilePath, 1, 0, "")
                    dt = ReadExcelToDT(FilePath, 0, 0, "")
                    RemoveEmptyRow(dt1:=dt)
                    '  Dim nganhnghe = (From q In data.DanhMucNganhNghes Where q.CAP5 IsNot Nothing Order By q.Name Select q.Id, q.Name).ToList
                    Dim _lstCMND As New List(Of String)
                    Dim _lstGCN As New List(Of String)
                    Dim MaHoSo, NgayDangKyLanDau, TenDonViKD, DiaChiKinhDoanh, DienThoai, Fax, Email, Website, Nganhghe, ChiTietNganhNghe, SoTien, SoTienBangChu, _
                           HoTen, GioiTinh, NgaySinh, DanToc, QuocTich, HoKhauThuongTru, ChoOHienTai, LoaiGiayTo, SoCMND, NgayCapCMND, NoiCapCMND, _
                           DonViCap, PhongBanCap, NguoiKy, ChucVu, XaPhuongId, NgayTao, Creater, NgaySua, Modifier, StatusId, LinhVucKinhDoanh, MaBienNhan, _
                           NguonTao, VonTienMat, VonTaiSan, NgayTraHoSo, SoBienNhan, IsMaSoThue As String
                    Dim _dateNow As DateTime = Now
                    Dim i = 0
                    For Each item As DataRow In dt.Rows
                        Try
                            Dim ch As New CertifiedHousehold
                            MaHoSo = item(1).ToString
                            NgayDangKyLanDau = item(2).ToString
                            TenDonViKD = item(3).ToString
                            DiaChiKinhDoanh = item(4).ToString
                            DienThoai = item(5).ToString
                            Fax = item(6).ToString
                            Email = item(7).ToString
                            Website = item(8).ToString
                            Nganhghe = item(9).ToString
                            ChiTietNganhNghe = item(10).ToString
                            SoTien = item(11).ToString
                            SoTienBangChu = item(12).ToString
                            HoTen = item(13).ToString
                            GioiTinh = item(14).ToString
                            NgaySinh = item(15).ToString
                            DanToc = item(16).ToString
                            QuocTich = item(17).ToString
                            HoKhauThuongTru = item(18).ToString
                            ChoOHienTai = item(19).ToString
                            LoaiGiayTo = item(20).ToString
                            SoCMND = item(21).ToString
                            NgayCapCMND = item(22).ToString
                            NoiCapCMND = item(23).ToString

                            'cap nhat tem import excel
                            DonViCap = item(24).ToString 'y
                            PhongBanCap = item(25).ToString 'z
                            NguoiKy = item(26).ToString
                            ChucVu = item(27).ToString
                            XaPhuongId = item(28).ToString
                            NgayTao = item(29).ToString
                            Creater = item(30).ToString
                            NgaySua = item(31).ToString
                            Modifier = item(32).ToString
                            StatusId = item(33).ToString
                            LinhVucKinhDoanh = item(34).ToString
                            MaBienNhan = item(35).ToString
                            NguonTao = item(36).ToString
                            VonTienMat = item(37).ToString
                            VonTaiSan = item(38).ToString
                            NgayTraHoSo = item(39).ToString
                            SoBienNhan = item(40).ToString
                            IsMaSoThue = item(41).ToString

                            If SoCMND.Trim <> "" AndAlso (From q In data.CertifiedHouseholds Where q.Ow_CardID = SoCMND.Trim).Count > 0 Then
                                _lstCMND.Add(SoCMND)
                            End If
                            'kiểm tra trùng GCN
                            If MaHoSo.Trim <> "" AndAlso (From q In data.CertifiedHouseholds Where q.CertifiedCode = MaHoSo.Trim).Count > 0 Then
                                _lstGCN.Add(MaHoSo)
                            End If

                            'KIỂM TRA NẾU CHỌN LOẠI LÀ CHẤM DỨT
                            If ddlLoai.SelectedValue = 2 Then ' chấm dứt

                                Dim _P = (From q In data.CertifiedHouseholds Where q.CertifiedCode = MaHoSo And q.ProvinceId = _curHuyenId And q.Ow_CardID = SoCMND Order By q.CertifiedUp_Time Descending Select q.CertifiedID, q.CertifiedUp_Time).FirstOrDefault
                                If Not _P Is Nothing Then
                                    Dim _cd = (From cd In data.CertifiedChamDuts Where cd.CertifiedCode = MaHoSo And cd.HuyenId = _curHuyenId Select cd).FirstOrDefault
                                    If _cd Is Nothing Then
                                        Dim cc As New CertifiedChamDut
                                        'insert CertifiedChamDut
                                        With cc
                                            .CertifiedID = _P.CertifiedID.ToString
                                            .CertifiedCode = MaHoSo
                                            .Nguoitao = Session("UserName").ToString
                                            '.Ow_CardID = SoCMND.ToString
                                            .StatusId = 4
                                            If IsDate(NgayDangKyLanDau) Then
                                                .NgayTao = DateTime.Parse(NgayDangKyLanDau)
                                            Else
                                                .NgayTao = StringToDate(NgayDangKyLanDau)
                                            End If
                                            If IsDate(NgayDangKyLanDau) Then
                                                .NgayChamDut = DateTime.Parse(NgayDangKyLanDau)
                                            Else
                                                .NgayChamDut = StringToDate(NgayDangKyLanDau)
                                            End If
                                            .HuyenId = _curHuyenId
                                            data.CertifiedChamDuts.AddObject(cc)
                                            data.SaveChanges()
                                        End With
                                    End If
                                Else
                                    'insert CertifiedHousehold
                                    Dim _ce As New CertifiedHousehold
                                    With ch
                                        .CertifiedCode = MaHoSo
                                        If IsDate(NgayDangKyLanDau) Then
                                            .CertifiedDate = DateTime.Parse(NgayDangKyLanDau)
                                        Else
                                            .CertifiedDate = StringToDate(NgayDangKyLanDau)
                                        End If
                                        .Biz_VietName = TenDonViKD.ToUpper
                                        .Biz_HeadOffice = DiaChiKinhDoanh
                                        .Biz_Tel = DienThoai
                                        .Biz_Fax = Fax
                                        .Biz_Email = Email
                                        .Biz_Website = Website
                                        .NganhNgheKinhDoanh = Nganhghe
                                        .Biz_InvestCapital = SoTien.Trim()
                                        .Biz_WordsMoney = SoTienBangChu.Trim()
                                        .Ow_Name = HoTen.ToUpper
                                        .Ow_Sex = IIf(GioiTinh = "Nam", 1, 0)
                                        .Ow_BirthDate = NgaySinh
                                        .Ow_National_ID = DanToc
                                        .Ow_Nationality_ID = QuocTich
                                        .Ow_Resident = HoKhauThuongTru
                                        .Ow_Present = ChoOHienTai
                                        .Ow_CardID = SoCMND
                                        If IsDate(NgayCapCMND) Then
                                            .Ow_CardDate = NgayCapCMND
                                        Else
                                            .Ow_CardDate = StringToDate(NgayCapCMND)
                                        End If
                                        'lấy công an cấp CMND viết tắt
                                        .Ow_Provider_ID = Change_Char_To_String(NoiCapCMND)
                                        .ProvinceId = Session("HuyenId")
                                        .Creator = Session("UserName").ToString
                                        If IsDate(NgayDangKyLanDau) Then
                                            .Created = DateTime.Parse(NgayDangKyLanDau)
                                        Else
                                            .Created = StringToDate(NgayDangKyLanDau)
                                        End If
                                        If IsDate(NgayDangKyLanDau) Then
                                            .NgayTraHoSo = DateTime.Parse(NgayDangKyLanDau)
                                        Else
                                            .NgayTraHoSo = StringToDate(NgayDangKyLanDau)
                                        End If
                                        .CertifiedStatus = "M"
                                        .CertifiedUp_Time = 0
                                        .StatusId = 4 ' da hoan tat
                                    End With
                                    data.CertifiedHouseholds.AddObject(ch)
                                    data.SaveChanges()

                                    'insert CertifiedChamDut
                                    Dim _cc As New CertifiedChamDut
                                    With _cc
                                        .CertifiedCode = MaHoSo
                                        .Nguoitao = Session("UserName").ToString()
                                        '.Ow_CardID = SoCMND.ToString
                                        .StatusId = 4
                                        If IsDate(NgayDangKyLanDau) Then
                                            .NgayTao = DateTime.Parse(NgayDangKyLanDau)
                                        Else
                                            .NgayTao = StringToDate(NgayDangKyLanDau)
                                        End If
                                        If IsDate(NgayDangKyLanDau) Then
                                            .NgayChamDut = DateTime.Parse(NgayDangKyLanDau)
                                        Else
                                            .NgayChamDut = StringToDate(NgayDangKyLanDau)
                                        End If
                                        .HuyenId = _curHuyenId
                                        data.CertifiedChamDuts.AddObject(_cc)
                                        data.SaveChanges()
                                    End With
                                End If

                            Else
                                ' KIỂM TRA NẾU LOẠI LÀ CẤP ĐỔI/CẤP MỚI
                                With ch
                                    .CertifiedCode = MaHoSo
                                    If IsDate(NgayDangKyLanDau) Then
                                        .CertifiedDate = DateTime.Parse(NgayDangKyLanDau)
                                    Else
                                        .CertifiedDate = StringToDate(NgayDangKyLanDau)
                                    End If
                                    .Biz_VietName = TenDonViKD.ToUpper
                                    .Biz_HeadOffice = DiaChiKinhDoanh
                                    .Biz_Tel = DienThoai
                                    .Biz_Fax = Fax
                                    .Biz_Email = Email
                                    .Biz_Website = Website
                                    .NganhNgheKinhDoanh = Nganhghe
                                    .Biz_InvestCapital = SoTien.Trim()
                                    .Biz_WordsMoney = SoTienBangChu.Trim()
                                    .Ow_Name = HoTen.ToUpper
                                    .Ow_Sex = IIf(GioiTinh = "Nam", 1, 0)
                                    .Ow_BirthDate = NgaySinh
                                    .Ow_National_ID = DanToc
                                    .Ow_Nationality_ID = QuocTich
                                    .Ow_Resident = HoKhauThuongTru
                                    .Ow_Present = ChoOHienTai
                                    .Ow_CardID = SoCMND
                                    If IsDate(NgayCapCMND) Then
                                        .Ow_CardDate = NgayCapCMND
                                    Else
                                        .Ow_CardDate = StringToDate(NgayCapCMND)
                                    End If
                                    'lấy công an cấp CMND viết tắt
                                    .Ow_Provider_ID = Change_Char_To_String(NoiCapCMND)
                                    .ProvinceId = Session("HuyenId")
                                    '.Creator = Session("UserName").ToString
                                    'kiem tra ngay tao
                                    If NgayTao = "" Or NgayTao Is Nothing Then
                                        NgayTao = NgayDangKyLanDau
                                    End If
                                    If IsDate(NgayTao) Then
                                        .Created = DateTime.Parse(NgayTao)
                                    Else
                                        .Created = StringToDate(NgayTao)
                                    End If
                                    'kiem tra ngay tra ho so
                                    If NgayTraHoSo = "" Or NgayTraHoSo Is Nothing Then
                                        NgayTraHoSo = NgayDangKyLanDau
                                    End If
                                    If IsDate(NgayTraHoSo) Then
                                        .NgayTraHoSo = DateTime.Parse(NgayTraHoSo)
                                    Else
                                        .NgayTraHoSo = StringToDate(NgayTraHoSo)
                                    End If
                                    If ddlLoai.SelectedValue = 0 Then
                                        .CertifiedStatus = "M"
                                        .CertifiedUp_Time = 0
                                    ElseIf ddlLoai.SelectedValue = 1 Then
                                        .CertifiedStatus = "D"
                                    End If

                                    .Modifier = Modifier
                                    If StatusId Is Nothing Or StatusId = "" Then
                                        .StatusId = 4
                                    Else
                                        .StatusId = StatusId
                                    End If
                                    '.StatusId = IIf(StatusId Is Nothing Or StatusId = "", 4, Integer.Parse(StatusId))
                                    If MaBienNhan Is Nothing Or MaBienNhan = "" Then
                                        .MaBienNhan = ""
                                    Else
                                        .MaBienNhan = MaBienNhan
                                    End If
                                    '.MaBienNhan = IIf(MaBienNhan Is Nothing Or MaBienNhan = "", "", MaBienNhan)
                                    '.NguonTao = IIf(NguonTao Is Nothing Or NguonTao = "", -1, Integer.Parse(NguonTao))
                                    If NguonTao Is Nothing Or NguonTao = "" Then
                                        .NguonTao = 0
                                    Else
                                        .NguonTao = NguonTao
                                    End If
                                    '.VonTienMat = IIf(VonTienMat Is Nothing Or VonTienMat = "", "", VonTienMat)
                                    If VonTienMat Is Nothing Or VonTienMat = "" Then
                                        .VonTienMat = 0
                                    Else
                                        .VonTienMat = VonTienMat
                                    End If
                                    '.VonTaiSan = IIf(NguonTao Is Nothing Or NguonTao = "", "", VonTaiSan)
                                    If VonTaiSan Is Nothing Or VonTaiSan = "" Then
                                        .VonTaiSan = 0
                                    Else
                                        .VonTaiSan = VonTaiSan
                                    End If
                                    '.CertifiedProviderOrg = IIf(NguonTao Is Nothing Or NguonTao = "", "", DonViCap)
                                    If DonViCap Is Nothing Or DonViCap = "" Then
                                        .CertifiedProviderOrg = ""
                                    Else
                                        .CertifiedProviderOrg = DonViCap
                                    End If
                                    '.CertifiedDeptName = IIf(NguonTao Is Nothing Or NguonTao = "", "", PhongBanCap)
                                    If PhongBanCap Is Nothing Or PhongBanCap = "" Then
                                        .CertifiedDeptName = ""
                                    Else
                                        .CertifiedDeptName = PhongBanCap
                                    End If
                                    ' .CertifiedSignerName = IIf(NguonTao Is Nothing Or NguonTao = "", "", NguoiKy)
                                    If NguoiKy Is Nothing Or NguoiKy = "" Then
                                        .CertifiedSignerName = ""
                                    Else
                                        .CertifiedSignerName = NguoiKy
                                    End If
                                    '.CertifiedSignerPosition = IIf(NguonTao Is Nothing Or NguonTao = "", "", ChucVu)
                                    If ChucVu Is Nothing Or ChucVu = "" Then
                                        .CertifiedSignerPosition = ""
                                    Else
                                        .CertifiedSignerPosition = ChucVu
                                    End If
                                    '.XaPhuongId = IIf(XaPhuongId Is Nothing Or XaPhuongId = "", 0, Integer.Parse(XaPhuongId))
                                    If XaPhuongId Is Nothing Or XaPhuongId = "" Then
                                        .XaPhuongId = 0
                                    Else
                                        .XaPhuongId = Integer.Parse(XaPhuongId)
                                    End If
                                    '.LinhVucKinhDoanh = IIf(LinhVucKinhDoanh Is Nothing Or LinhVucKinhDoanh = "", "", Integer.Parse(LinhVucKinhDoanh))
                                    If LinhVucKinhDoanh Is Nothing Or LinhVucKinhDoanh = "" Then
                                        .LinhVucKinhDoanh = 0
                                    Else
                                        .LinhVucKinhDoanh = Integer.Parse(LinhVucKinhDoanh)
                                    End If

                                    If Creater = "" Or Creater Is Nothing Then
                                        .Creator = Session("UserName").ToString
                                    Else
                                        .Creator = Creater
                                    End If
                                    '.Creator = Creater
                                    '.SoBienNhan = SoBienNhan
                                    If SoBienNhan = "" Or SoBienNhan Is Nothing Then
                                        .SoBienNhan = ""
                                    Else
                                        .SoBienNhan = SoBienNhan
                                    End If
                                    If Not IsMaSoThue Is Nothing Or IsMaSoThue <> "" Then
                                        .IsMaSoThue = Byte.Parse(IsMaSoThue)
                                    Else
                                        .IsMaSoThue = 0
                                    End If
                                    '                                

                                    ' .StatusId = 4 ' da hoan tat

                                End With
                                Dim _kt = (From q In data.CertifiedHouseholds Where q.CertifiedCode = MaHoSo And q.ProvinceId = _curHuyenId And q.Ow_CardID = SoCMND Select q).FirstOrDefault
                                If (_kt Is Nothing And ddlLoai.SelectedValue = 0) Or ddlLoai.SelectedValue = 1 Then
                                    data.CertifiedHouseholds.AddObject(ch)
                                    data.SaveChanges()
                                End If

                                Dim chh As CertifiedHousehold
                                Dim _cerId As Integer = ch.CertifiedID
                                If ddlLoai.SelectedValue = 0 Then
                                    chh = (From q In data.CertifiedHouseholds Where q.CertifiedID = _cerId Select q).FirstOrDefault
                                    chh.ParentId = _cerId ' ho so dang ky lan parentId = CerId
                                ElseIf ddlLoai.SelectedValue = 1 Then
                                    Dim _P = (From q In data.CertifiedHouseholds Where q.CertifiedCode = MaHoSo And q.ProvinceId = _curHuyenId And q.CertifiedID <> _cerId Order By q.CertifiedUp_Time Descending Select q.CertifiedID, q.CertifiedUp_Time).FirstOrDefault
                                    If Not _P Is Nothing Then
                                        chh = (From q In data.CertifiedHouseholds Where q.CertifiedID = _cerId Select q).FirstOrDefault
                                        chh.ParentId = (From q In data.CertifiedHouseholds Where q.CertifiedCode = MaHoSo And q.ProvinceId = _curHuyenId And q.CertifiedID <> _cerId Order By q.CertifiedUp_Time Ascending Select q.CertifiedID).FirstOrDefault
                                        chh.CertifiedUp_Time = _P.CertifiedUp_Time + 1
                                    Else
                                        chh = (From q In data.CertifiedHouseholds Where q.CertifiedID = _cerId Select q).FirstOrDefault
                                        chh.ParentId = chh.CertifiedID
                                        chh.CertifiedUp_Time = 0
                                        chh.CertifiedStatus = "M"
                                        data.SaveChanges()
                                        Dim chcd As New CertifiedHousehold
                                        With chcd
                                            .CertifiedCode = MaHoSo
                                            .CertifiedDate = StringToDate(NgayDangKyLanDau)
                                            .Biz_VietName = TenDonViKD.ToUpper
                                            .Biz_HeadOffice = DiaChiKinhDoanh
                                            .Biz_Tel = DienThoai
                                            .Biz_Fax = Fax
                                            .Biz_Email = Email
                                            .Biz_Website = Website
                                            .NganhNgheKinhDoanh = Nganhghe
                                            .Biz_InvestCapital = SoTien.Trim()
                                            .Biz_WordsMoney = SoTienBangChu.Trim()
                                            .Ow_Name = HoTen.ToUpper
                                            .Ow_Sex = IIf(GioiTinh = "Nam", 1, 0)
                                            .Ow_BirthDate = NgaySinh
                                            .Ow_National_ID = DanToc
                                            .Ow_Nationality_ID = QuocTich
                                            .Ow_Resident = HoKhauThuongTru
                                            .Ow_Present = ChoOHienTai
                                            .Ow_CardID = SoCMND
                                            .Ow_CardDate = StringToDate(NgayCapCMND)
                                            'lấy công an cấp CMND viết tắt
                                            .Ow_Provider_ID = Change_Char_To_String(NoiCapCMND)
                                            .ProvinceId = Session("HuyenId")
                                            '.Creator = Session("UserName").ToString
                                            '.Created = StringToDate(NgayDangKyLanDau)
                                            '.NgayTraHoSo = StringToDate(NgayDangKyLanDau)
                                            .CertifiedStatus = "D"
                                            '.StatusId = 4 ' da hoan tat
                                            '.MaBienNhan = MaBienNhan 'thêm Mã BN

                                            'kiem tra ngay tao
                                            If NgayTao = "" Or NgayTao Is Nothing Then
                                                NgayTao = NgayDangKyLanDau
                                            End If
                                            If IsDate(NgayTao) Then
                                                .Created = DateTime.Parse(NgayTao)
                                            Else
                                                .Created = StringToDate(NgayTao)
                                            End If
                                            'kiem tra ngay tra ho so
                                            If NgayTraHoSo = "" Or NgayTraHoSo Is Nothing Then
                                                NgayTraHoSo = NgayDangKyLanDau
                                            End If
                                            If IsDate(NgayTraHoSo) Then
                                                .NgayTraHoSo = DateTime.Parse(NgayTraHoSo)
                                            Else
                                                .NgayTraHoSo = StringToDate(NgayTraHoSo)
                                            End If
                                            If ddlLoai.SelectedValue = 0 Then
                                                .CertifiedStatus = "M"
                                                .CertifiedUp_Time = 0
                                            ElseIf ddlLoai.SelectedValue = 1 Then
                                                .CertifiedStatus = "D"
                                            End If

                                            .Modifier = Modifier
                                            If StatusId Is Nothing Or StatusId = "" Then
                                                .StatusId = 4
                                            Else
                                                .StatusId = StatusId
                                            End If
                                            '.StatusId = IIf(StatusId Is Nothing Or StatusId = "", 4, Integer.Parse(StatusId))
                                            If MaBienNhan Is Nothing Or MaBienNhan = "" Then
                                                .MaBienNhan = ""
                                            Else
                                                .MaBienNhan = MaBienNhan
                                            End If
                                            '.MaBienNhan = IIf(MaBienNhan Is Nothing Or MaBienNhan = "", "", MaBienNhan)
                                            '.NguonTao = IIf(NguonTao Is Nothing Or NguonTao = "", -1, Integer.Parse(NguonTao))
                                            If NguonTao Is Nothing Or NguonTao = "" Then
                                                .NguonTao = 0
                                            Else
                                                .NguonTao = NguonTao
                                            End If
                                            '.VonTienMat = IIf(VonTienMat Is Nothing Or VonTienMat = "", "", VonTienMat)
                                            If VonTienMat Is Nothing Or VonTienMat = "" Then
                                                .VonTienMat = 0
                                            Else
                                                .VonTienMat = VonTienMat
                                            End If
                                            '.VonTaiSan = IIf(NguonTao Is Nothing Or NguonTao = "", "", VonTaiSan)
                                            If VonTaiSan Is Nothing Or VonTaiSan = "" Then
                                                .VonTaiSan = 0
                                            Else
                                                .VonTaiSan = VonTaiSan
                                            End If
                                            '.CertifiedProviderOrg = IIf(NguonTao Is Nothing Or NguonTao = "", "", DonViCap)
                                            If DonViCap Is Nothing Or DonViCap = "" Then
                                                .CertifiedProviderOrg = ""
                                            Else
                                                .CertifiedProviderOrg = DonViCap
                                            End If
                                            '.CertifiedDeptName = IIf(NguonTao Is Nothing Or NguonTao = "", "", PhongBanCap)
                                            If PhongBanCap Is Nothing Or PhongBanCap = "" Then
                                                .CertifiedDeptName = ""
                                            Else
                                                .CertifiedDeptName = PhongBanCap
                                            End If
                                            ' .CertifiedSignerName = IIf(NguonTao Is Nothing Or NguonTao = "", "", NguoiKy)
                                            If NguoiKy Is Nothing Or NguoiKy = "" Then
                                                .CertifiedSignerName = ""
                                            Else
                                                .CertifiedSignerName = NguoiKy
                                            End If
                                            '.CertifiedSignerPosition = IIf(NguonTao Is Nothing Or NguonTao = "", "", ChucVu)
                                            If ChucVu Is Nothing Or ChucVu = "" Then
                                                .CertifiedSignerPosition = ""
                                            Else
                                                .CertifiedSignerPosition = ChucVu
                                            End If
                                            '.XaPhuongId = IIf(XaPhuongId Is Nothing Or XaPhuongId = "", 0, Integer.Parse(XaPhuongId))
                                            If XaPhuongId Is Nothing Or XaPhuongId = "" Then
                                                .XaPhuongId = 0
                                            Else
                                                .XaPhuongId = Integer.Parse(XaPhuongId)
                                            End If
                                            '.LinhVucKinhDoanh = IIf(LinhVucKinhDoanh Is Nothing Or LinhVucKinhDoanh = "", "", Integer.Parse(LinhVucKinhDoanh))
                                            If LinhVucKinhDoanh Is Nothing Or LinhVucKinhDoanh = "" Then
                                                .LinhVucKinhDoanh = 0
                                            Else
                                                .LinhVucKinhDoanh = Integer.Parse(LinhVucKinhDoanh)
                                            End If

                                            If Creater = "" Or Creater Is Nothing Then
                                                .Creator = Session("UserName").ToString
                                            Else
                                                .Creator = Creater
                                            End If
                                            '.Creator = Creater
                                            '.SoBienNhan = SoBienNhan
                                            If SoBienNhan = "" Or SoBienNhan Is Nothing Then
                                                .SoBienNhan = ""
                                            Else
                                                .SoBienNhan = SoBienNhan
                                            End If
                                            If Not IsMaSoThue Is Nothing Or IsMaSoThue <> "" Then
                                                .IsMaSoThue = Byte.Parse(IsMaSoThue)
                                            Else
                                                .IsMaSoThue = 0
                                            End If



                                        End With
                                        data.CertifiedHouseholds.AddObject(chcd)
                                        data.SaveChanges()
                                        Dim _cerUp_Time = (From q In data.CertifiedHouseholds Where q.CertifiedCode = MaHoSo And q.ProvinceId = _curHuyenId Order By q.CertifiedUp_Time Descending Select q.CertifiedID, q.CertifiedUp_Time).FirstOrDefault
                                        chcd.ParentId = _cerId
                                        chcd.CertifiedUp_Time = _cerUp_Time.CertifiedUp_Time + 1
                                        'update address
                                        InsertAddress(chcd.CertifiedID)
                                    End If

                                End If
                                data.SaveChanges()
                                'update address
                                InsertAddress(ch.CertifiedID)
                            End If
                        Catch ex As Exception
                        End Try
                        i = i + 1
                    Next
                    '' Save data
                    'End If
                    '' Hiển thị danh sách các Item trùng CMND
                    If Not IsNothing(_lstCMND) AndAlso _lstCMND.Count > 0 Then
                        ltrExistsCMND.Text = String.Join("</br>", _lstCMND.ToArray)
                        ltrExistsGCN.Text = String.Join("</br>", _lstGCN.ToArray)
                        Excute_Javascript("Alertbox('Cập nhật liệu thành công. " + i.ToString + " dòng');showHide(1)", Me.Page, True)
                    End If
                    GC.Collect() 'gọi giải phóng bộ nhớ tức thì
                Catch ex As Exception
                    log4net.Config.XmlConfigurator.Configure()
                    log.Error("Error error " & AddTabSpace(1) & Session("Username") & AddTabSpace(1) & "IP:" & GetIPAddress(), ex)
                    Excute_Javascript("Alertbox('Xãy ra lỗi trong lúc xử lý dữ liệu file. \n " & ex.Message & "');", Me.Page, True)
                Finally
                    '' final-> Delete file upload
                    If File.Exists(FilePath) Then
                        File.Delete(FilePath)
                    End If
                End Try
            End Using
        End If
    End Sub
    Protected Sub InsertAddress(ByVal _cerId As Integer)
        Using _data As New VSEntities
            Dim ch = (From q In _data.CertifiedHouseholds Where q.CertifiedID = _cerId Select q).FirstOrDefault
            Try
                Dim _thuongtru As String() = ch.Ow_Resident.ToString.Split(",")
                Dim _tamtru As String() = ch.Ow_Present.ToString.Split(",")
                Dim _DDKD As String() = ch.Biz_HeadOffice.ToString.Split(",")

                Dim _tinh As String
                Dim _Huyen As String
                Dim _Xa As String

                _tinh = _thuongtru(_thuongtru.Length - 1).Trim()
                Dim _strTinh As String = ""
                If _tinh = "An Giang" Then
                    _strTinh = "tỉnh An Giang"
                Else
                    _strTinh = _tinh
                End If
                _Huyen = _thuongtru(_thuongtru.Length - 2).Trim()
                Dim _strHuyen As String = ""
                If _Huyen = "TP Long Xuyên" Then
                    _strHuyen = "thành phố Long Xuyên"
                Else
                    _strHuyen = _Huyen
                End If

                Dim _strXa As String = ""
                If _thuongtru.Length > 3 Then
                    _Xa = _thuongtru(_thuongtru.Length - 3).Trim()
                    If _Xa = "Bình Đức" Or _Xa = "Phường Bình Đức" Then
                        _strXa = "phường Bình Đức"
                    ElseIf _Xa = "Bình Khánh" Or _Xa = "Phường Bình Khánh" Then
                        _strXa = "phường Bình Khánh"
                    ElseIf _Xa = "Đông Xuyên" Or _Xa = "Phường Đông Xuyên" Then
                        _strXa = "phường Đông Xuyên"
                    ElseIf _Xa = "Mỹ Bình" Or _Xa = "Phường Mỹ Bình" Then
                        _strXa = "phường Mỹ Bình"
                    ElseIf _Xa = "Mỹ Hoà" Or _Xa = "Phường Mỹ Hoà" Then
                        _strXa = "phường Mỹ Hoà"
                    ElseIf _Xa = "Mỹ Long" Or _Xa = "Phường Mỹ Long" Then
                        _strXa = "phường Mỹ Long"
                    ElseIf _Xa = "Mỹ Phước" Or _Xa = "Phường Mỹ Phước" Then
                        _strXa = "phường Mỹ Phước"
                    ElseIf _Xa = "Mỹ Quý" Or _Xa = "Phường Mỹ Quý" Then
                        _strXa = "phường Mỹ Quý"
                    ElseIf _Xa = "Mỹ Thạnh" Or _Xa = "Phường Mỹ Thạnh" Then
                        _strXa = "phường Mỹ Thạnh"
                    ElseIf _Xa = "Mỹ Thới" Or _Xa = "Phường Mỹ Thới" Then
                        _strXa = "phường Mỹ Thới"
                    ElseIf _Xa = "Mỹ Xuyên" Or _Xa = "Phường Mỹ Xuyên" Then
                        _strXa = "phường Mỹ Xuyên"
                    ElseIf _Xa = "Mỹ Hoà Hưng" Or _Xa = "Xã Mỹ Hoà Hưng" Then
                        _strXa = "xã Mỹ Hoà Hưng"
                    ElseIf _Xa = "Mỹ Khánh" Or _Xa = "Xã Mỹ Khánh" Then
                        _strXa = "xã Mỹ Khánh"
                    Else
                        _strXa = _Xa
                    End If
                Else
                    _Xa = ""
                End If
                'thường trú
                Dim tht As New VSModel.CertifiedHouseholdAddress
                tht.CertifiedID = ch.CertifiedID
                tht.TypeId = LoaiDiaChi.ThuongTru
                Dim _Addresses = (From q In _data.CertifiedHouseholds Where q.CertifiedID = _cerId Select q).FirstOrDefault
                tht.TinhId = (From h In _data.Provinces Where h.Title.ToUpper = _strTinh.ToUpper Select h.Id).FirstOrDefault
                'khi set = javascript se ko lay dc  phai set vao hidden field
                tht.HuyenId = (From h In _data.Provinces Where h.Title.ToUpper = _strHuyen.ToUpper Select h.Id).FirstOrDefault
                tht.XaThiTranId = (From h In _data.Provinces Where h.Title.ToUpper = _strXa.ToUpper Select h.Id).FirstOrDefault
                tht.Adress = ch.Ow_Resident.ToString.Replace(_tinh, "").Replace(_Huyen, "").Replace(_Xa, "").Trim().TrimEnd(",").Trim().TrimEnd(",").Trim().TrimEnd(",").Trim().TrimEnd(",")

                _data.CertifiedHouseholdAddresses.AddObject(tht)
                _Addresses.Ow_Resident = tht.Adress & "," & _strXa & "," & _strHuyen & "," & _strTinh
                _data.SaveChanges()

                _tinh = _tamtru(_tamtru.Length - 1).Trim()
                If _tinh = "An Giang" Then
                    _strTinh = "tỉnh An Giang"
                Else
                    _strTinh = _tinh
                End If
                _Huyen = _tamtru(_tamtru.Length - 2).Trim()
                If _Huyen = "TP Long Xuyên" Then
                    _strHuyen = "thành phố Long Xuyên"
                Else
                    _strHuyen = _Huyen
                End If
                If _tamtru.Length > 3 Then
                    _Xa = _tamtru(_tamtru.Length - 3).Trim()
                    If _Xa = "Bình Đức" Or _Xa = "Phường Bình Đức" Then
                        _strXa = "phường Bình Đức"
                    ElseIf _Xa = "Bình Khánh" Or _Xa = "Phường Bình Khánh" Then
                        _strXa = "phường Bình Khánh"
                    ElseIf _Xa = "Đông Xuyên" Or _Xa = "Phường Đông Xuyên" Then
                        _strXa = "phường Đông Xuyên"
                    ElseIf _Xa = "Mỹ Bình" Or _Xa = "Phường Mỹ Bình" Then
                        _strXa = "phường Mỹ Bình"
                    ElseIf _Xa = "Mỹ Hoà" Or _Xa = "Phường Mỹ Hoà" Then
                        _strXa = "phường Mỹ Hoà"
                    ElseIf _Xa = "Mỹ Long" Or _Xa = "Phường Mỹ Long" Then
                        _strXa = "phường Mỹ Long"
                    ElseIf _Xa = "Mỹ Phước" Or _Xa = "Phường Mỹ Phước" Then
                        _strXa = "phường Mỹ Phước"
                    ElseIf _Xa = "Mỹ Quý" Or _Xa = "Phường Mỹ Quý" Then
                        _strXa = "phường Mỹ Quý"
                    ElseIf _Xa = "Mỹ Thạnh" Or _Xa = "Phường Mỹ Thạnh" Then
                        _strXa = "phường Mỹ Thạnh"
                    ElseIf _Xa = "Mỹ Thới" Or _Xa = "Phường Mỹ Thới" Then
                        _strXa = "phường Mỹ Thới"
                    ElseIf _Xa = "Mỹ Xuyên" Or _Xa = "Phường Mỹ Xuyên" Then
                        _strXa = "phường Mỹ Xuyên"
                    ElseIf _Xa = "Mỹ Hoà Hưng" Or _Xa = "Xã Mỹ Hoà Hưng" Then
                        _strXa = "xã Mỹ Hoà Hưng"
                    ElseIf _Xa = "Mỹ Khánh" Or _Xa = "Xã Mỹ Khánh" Then
                        _strXa = "xã Mỹ Khánh"
                    Else
                        _strXa = _Xa
                    End If
                Else
                    _Xa = ""
                End If
                'tam trú
                Dim tt As New VSModel.CertifiedHouseholdAddress
                tt.CertifiedID = ch.CertifiedID
                tt.TypeId = LoaiDiaChi.TamTru

                tt.TinhId = (From h In _data.Provinces Where h.Title.ToUpper = _strTinh.ToUpper Select h.Id).FirstOrDefault
                'khi set = javascript se ko lay dc  phai set vao hidden field
                tt.HuyenId = (From h In _data.Provinces Where h.Title.ToUpper = _strHuyen.ToUpper Select h.Id).FirstOrDefault
                tt.XaThiTranId = (From h In _data.Provinces Where h.Title.ToUpper = _strXa.ToUpper Select h.Id).FirstOrDefault
                tt.Adress = ch.Ow_Present.ToString.Replace(_tinh, "").Replace(_Huyen, "").Replace(_Xa, "").TrimEnd(",").Trim().TrimEnd(",").Trim().TrimEnd(",").Trim().TrimEnd(",").Trim().TrimEnd(",")

                _data.CertifiedHouseholdAddresses.AddObject(tt)
                _Addresses.Ow_Present = tt.Adress & "," & _strXa & "," & _strHuyen & "," & _strTinh
                _data.SaveChanges()

                _tinh = _DDKD(_DDKD.Length - 1).Trim()
                If _tinh = "An Giang" Then
                    _strTinh = "tỉnh An Giang"
                Else
                    _strTinh = _tinh
                End If
                _Huyen = _DDKD(_DDKD.Length - 2).Trim()
                If _Huyen = "TP Long Xuyên" Then
                    _strHuyen = "thành phố Long Xuyên"
                Else
                    _strHuyen = _Huyen
                End If
                If _DDKD.Length > 3 Then
                    _Xa = _DDKD(_DDKD.Length - 3).Trim()
                    If _Xa = "Bình Đức" Or _Xa = "Phường Bình Đức" Then
                        _strXa = "phường Bình Đức"
                    ElseIf _Xa = "Bình Khánh" Or _Xa = "Phường Bình Khánh" Then
                        _strXa = "phường Bình Khánh"
                    ElseIf _Xa = "Đông Xuyên" Or _Xa = "Phường Đông Xuyên" Then
                        _strXa = "phường Đông Xuyên"
                    ElseIf _Xa = "Mỹ Bình" Or _Xa = "Phường Mỹ Bình" Then
                        _strXa = "phường Mỹ Bình"
                    ElseIf _Xa = "Mỹ Hoà" Or _Xa = "Phường Mỹ Hoà" Then
                        _strXa = "phường Mỹ Hoà"
                    ElseIf _Xa = "Mỹ Long" Or _Xa = "Phường Mỹ Long" Then
                        _strXa = "phường Mỹ Long"
                    ElseIf _Xa = "Mỹ Phước" Or _Xa = "Phường Mỹ Phước" Then
                        _strXa = "phường Mỹ Phước"
                    ElseIf _Xa = "Mỹ Quý" Or _Xa = "Phường Mỹ Quý" Then
                        _strXa = "phường Mỹ Quý"
                    ElseIf _Xa = "Mỹ Thạnh" Or _Xa = "Phường Mỹ Thạnh" Then
                        _strXa = "phường Mỹ Thạnh"
                    ElseIf _Xa = "Mỹ Thới" Or _Xa = "Phường Mỹ Thới" Then
                        _strXa = "phường Mỹ Thới"
                    ElseIf _Xa = "Mỹ Xuyên" Or _Xa = "Phường Mỹ Xuyên" Then
                        _strXa = "phường Mỹ Xuyên"
                    ElseIf _Xa = "Mỹ Hoà Hưng" Or _Xa = "Xã Mỹ Hoà Hưng" Then
                        _strXa = "xã Mỹ Hoà Hưng"
                    ElseIf _Xa = "Mỹ Khánh" Or _Xa = "Xã Mỹ Khánh" Then
                        _strXa = "xã Mỹ Khánh"
                    Else
                        _strXa = _Xa
                    End If
                Else
                    _Xa = ""
                End If
                'địa điểm kinh doanh
                Dim ddkd As New VSModel.CertifiedHouseholdAddress
                ddkd.CertifiedID = ch.CertifiedID
                ddkd.TypeId = LoaiDiaChi.DiaDiemKinhDoanh

                ddkd.TinhId = (From h In _data.Provinces Where h.Title.ToUpper = _strTinh.ToUpper Select h.Id).FirstOrDefault
                'khi set = javascript se ko lay dc  phai set vao hidden field
                ddkd.HuyenId = (From h In _data.Provinces Where h.Title.ToUpper = _strHuyen.ToUpper Select h.Id).FirstOrDefault
                ddkd.XaThiTranId = (From h In _data.Provinces Where h.Title.ToUpper = _strXa.ToUpper Select h.Id).FirstOrDefault
                ddkd.Adress = ch.Biz_HeadOffice.ToString.Replace(_tinh, "").Replace(_Huyen, "").Replace(_Xa, "").TrimEnd(",").Trim().TrimEnd(",").Trim().TrimEnd(",").Trim().TrimEnd(",").Trim().TrimEnd(",")

                _data.CertifiedHouseholdAddresses.AddObject(ddkd)
                _Addresses.Biz_HeadOffice = ddkd.Adress & "," & _strXa & "," & _strHuyen & "," & _strTinh
                _data.SaveChanges()
            Catch ex As Exception
                ch.Ref2 = 2
                _data.SaveChanges()
            End Try
        End Using
    End Sub
End Class
